package net.admin.db.item;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class CustDAO {
	private ResultSet rs;
	private Connection conn;
	private Statement stmt;
	public CustDAO() throws Exception{
		Context initCtx=new InitialContext();
		Context envCtx=(Context)initCtx.lookup("java:comp/env");
		DataSource ds=(DataSource)envCtx.lookup("jdbc/OracleDB");
		this.conn=ds.getConnection();
		this.stmt=conn.createStatement();
	}
	
	public boolean isExistCust(CustBean crb) throws SQLException{
		StringBuffer query = new StringBuffer();
		query.append("select count(*) from cust_info where corp_no='" + crb.getCorp_no() + "'");
		rs=stmt.executeQuery(query.toString());
		rs.next();
		return rs.getInt(1)>0?true:false;
	}
	
	public boolean registCust(CustBean cb) throws SQLException{
		StringBuffer query = new StringBuffer();
		query.append("insert into cust_info ")
		.append("(cust_nm, ceo_nm, corp_no, user_tel, corp_addr, memo, admin_id, reg_date) ")
		.append("values(?,?,?,?,?,?,?,sysdate)");
		conn.setAutoCommit(false);
		PreparedStatement psmt = conn.prepareStatement(query.toString());
		psmt.setString(1, cb.getCust_nm());
		psmt.setString(2, cb.getCeo_nm());
		psmt.setString(3, cb.getCorp_no());
		psmt.setString(4, cb.getUser_tel());
		psmt.setString(5, cb.getCorp_addr());
		psmt.setString(6, cb.getEtc());
		psmt.setString(7, cb.getAdmin_id());
		int updateCnt=psmt.executeUpdate();
		
		if(updateCnt==1){
			conn.commit();
			return true;
		}else{
			conn.rollback();
			return false;
		}
	}
	
	public ArrayList getCustList(String cust_nm) throws SQLException{
		ArrayList custList = new ArrayList();
		StringBuffer query = new StringBuffer();
		String where = "";
		if(cust_nm!=null)where = "where cust_nm like '%" + cust_nm + "%' ";
		query
		.append("select cust_nm, ceo_nm, corp_addr, corp_no ")
		.append("from cust_info ")
		.append(where)
		.append("order by 1,3,4");
		rs=stmt.executeQuery(query.toString());
		CustBean cb = null;
		while(rs.next()){
			cb = new CustBean();
			cb.setCust_nm(rs.getString(1));
			cb.setCeo_nm(rs.getString(2));
			cb.setCorp_addr(rs.getString(3));
			cb.setCorp_no(rs.getString(4));
			custList.add(cb);
		}
		rs.close();
		return custList;
	}
	public void close(){
		try{
			if(rs!=null)rs.close();
			if(stmt!=null)stmt.close();
			if(conn!=null)conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}
